//
//  sqManager.m
//  HGY
//
//  Created by lanou3g on 15/11/1.
//  Copyright © 2015年 顾成辉. All rights reserved.
//

#import "sqManager.h"

@implementation sqManager




+ (sqManager *)shareManager
{
    static sqManager *share = nil;
    if (share == nil) {
        share = [[sqManager alloc] init];
    }
    return share;

}
static sqlite3 *db = nil;
- (sqlite3 *)openDB
{
    if (db != nil) {
        return db;
    }
    NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *dbPath = [documentsPath stringByAppendingPathComponent:@"sqMenu.sqlite"];
    NSLog(@"%@", dbPath);
    int result = sqlite3_open(dbPath.UTF8String ,&db);
    if (result == SQLITE_OK) {
        NSLog(@"创建成功");
    } else {
        NSLog(@"创建失败");
    }
    return db;
}
- (void)closeDB
{
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"关闭成功");
        db = nil;
    } else {
        NSLog(@"关闭失败");
    }
}

/*
 @property (nonatomic, retain)NSString *category;
 @property (nonatomic, retain)NSString *title;
 @property (nonatomic, retain)NSString *thumb;
 @property (nonatomic, retain)NSString *yingyang;
 @property (nonatomic, retain)NSString *yuanliao;
 @property (nonatomic, retain)NSString *effect;
 
 
 
 */

- (void)createTable
{
    [self openDB];
    NSString *sql = @"create table IF NOT EXISTS MenuModel(title text primary key not NULL, category text not NULL, thumb_2 text not NULL, yingyang text not NULL, yuanliao text not NULL, effect text not NULL)";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    } else {
        NSLog(@"创建表失败");
    }
    [self closeDB];
}
- (void)insetWith:(MenuModel *)model
{
    db = [self openDB];
    NSString *sql = [NSString stringWithFormat:@"insert into MenuModel(title, category, thumb_2, yingyang, yuanliao, effect) values ('%@', '%@', '%@', '%@', '%@', '%@' )", model.title, model.category, model.thumb, model.yingyang, model.yuanliao, model.effect];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }
    [self closeDB];
}
- (NSMutableArray *)queryAll
{
    [self openDB];
    NSString *sql = [NSString stringWithFormat:@"select * from MenuModel"];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        NSMutableArray *array = [NSMutableArray array];
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            char *title = (char *)sqlite3_column_text(stmt, 0);
            char *category = (char *)sqlite3_column_text(stmt, 1);
            char *thumb_2 = (char *)sqlite3_column_text(stmt, 2);
            char *yingyang = (char *)sqlite3_column_text(stmt, 3);
            char *yuanliao = (char *)sqlite3_column_text(stmt, 4);
            char *effect = (char *)sqlite3_column_text(stmt, 5);
            MenuModel *model = [[MenuModel alloc] init];
            model.title = [NSString stringWithUTF8String:title];
            model.category = [NSString stringWithUTF8String:category];
            model.thumb_2 = [NSString stringWithUTF8String:thumb_2];
            model.yingyang = [NSString stringWithUTF8String:yingyang];
            model.yuanliao = [NSString stringWithUTF8String:yuanliao];
            model.effect = [NSString stringWithUTF8String:effect];
            [array addObject:model];
        }
        sqlite3_finalize(stmt);
        [self closeDB];
        return array;
    } else {
        NSLog(@"查询失败");
    }
    [self closeDB];
    return nil;
}


- (void)deleteStudentWithString:(NSString *)string
{
    // 1.打开数据库
    [self openDB];
    // 2.写sql语句
    NSString *sql = [NSString stringWithFormat: @"delete from MenuModel where title = '%@'", string ];
    // 3.执行语句
    int result = sqlite3_exec(db , sql.UTF8String, NULL, NULL, NULL);
    // 4.判断是否成功
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除不成功");
    }
    // 5.关闭数据库
    [self closeDB];
    
    
    
}



@end
